﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Manoso.Data.FluentMap.Adaptor
{
    public class MsMapAdaptor : IMapAdaptor
    {
        public string ReturnValue { get { return "RETURN_VALUE"; } }
        public string ParameterPrefix { get {return "@";} }

        public string Translate(object arg)
        {
            var format = "{0}";

            if (arg is Enum)
                arg = Convert.ChangeType(arg, ((Enum)arg).GetTypeCode());
            else if (arg is DBNull || arg == null)
                format = "null";
            else if (arg is DateTime)
                format = "'{0:dd/MMM/yy H:mm:ss}'";
            else if (arg is string || arg is Guid)
                format = "'{0}'";
            else if (arg is bool)
                format = (bool)arg ? "1" : "0";
            else
            {
                var bytes = arg as byte[];
                if (bytes != null)
                    format = ToHex(bytes);
            }

            return string.Format(format, arg);
        }

        public string EscapeSql(object value)
        {
            const string quote = "'";

            var strVal = value.ToString();
            var reserves = new[] { new[] { quote, "''" }, new[] { "\n", "\\n" } };
            var quoted = strVal.StartsWith(quote) && strVal.EndsWith(quote) && strVal.Length > 1;
            var text = quoted ? strVal.Substring(1, strVal.Length - 2) : strVal;
            var result = reserves.Aggregate(text, (s, r) => s.Replace(r[0], r[1]));
            return quoted ? quote + result + quote : result;
        }

        public string GetExec(string storeProc, IEnumerable<IMapColumn> columns)
        {
            var array = LoadValues(columns);
            return string.Format("Exec [{0}] {1}{2}", storeProc, SqlParam(array.Select(x => x.Name), array.Select(x => x.Value)), Environment.NewLine);
        }

        public string GetExecReturn(string storeProc, string idColumn, IEnumerable<IMapColumn> columns)
        {
            var array = LoadValues(columns);
            return string.Format("Exec {0}{1} = [{2}] {3}{4}", ParameterPrefix, idColumn, storeProc, SqlParam(array.Select(x => x.Name), array.Select(x => x.Value)), Environment.NewLine);
        }

        public string GetReturn(string table, string alias, IEnumerable<IMapColumn> columns, IMapColumn idColumn)
        {
            var whereClause = string.Format("[{0}] = {1}", idColumn.Name, idColumn.Value == null ? ParameterPrefix + idColumn.Alias : Translate(idColumn.Value));
            return GetSelect(table, alias, whereClause, columns, null);
        }

        public string GetDeclare(Type type, string name)
        {
            var dbType = type == typeof(long) ? "bigint" : type == typeof(int) ? "int" : type == typeof(short) ? "smallint" : type == typeof(byte) ? "tinyint" : type == typeof(Guid) ? "uniqueidentifier" : "nvarchar(max)";
            return string.Format("Declare {0}{1} {2}{3}", ParameterPrefix, name, dbType, Environment.NewLine);
        }

        public string GetIdentity(string idColumn)
        {
            return string.Format("Set {0}{1} = SCOPE_IDENTITY(){2}", ParameterPrefix, idColumn, Environment.NewLine);
        }

        public string GetAssignment(IMapColumn column)
        {
            LoadValue(column);
            return string.Format("Set {0}{1} = {2}{3}", ParameterPrefix, column.Alias, column.Value, Environment.NewLine);
        }

        public string GetInsert(string table, IEnumerable<IMapColumn> columns)
        {
            var array = LoadValues(columns);
            return string.Format("Insert Into [{0}] {1}{2}", table, SqlValues(array.Select(x => x.Name), array.Select(x => x.Value)), Environment.NewLine);
        }

        public string GetUpdate(string table, IEnumerable<IMapColumn> idColumns, IEnumerable<IMapColumn> columns)
        {
            var array = LoadValues(columns);
            var arrayIds = LoadValues(idColumns);
            var sets = SqlSet(array.Select(x => x.Name), array.Select(x => x.Value));
            var where = SqlWhere(arrayIds.Select(x => x.Name), arrayIds.Select(x => x.Value));
            return string.Format("Update [{0}] Set {1} Where {2}{3}", table, sets, where, Environment.NewLine);
        }

        public string GetDelete(string table, IEnumerable<IMapColumn> idColumns)
        {
            var array = LoadValues(idColumns);
            return string.Format("Delete From [{0}] Where {1}{2}", table, SqlWhere(array.Select(x => x.Name), array.Select(x => x.Value)), Environment.NewLine);
        }

        public string GetLeftJoin(string tableSource, string aliasSource, string aliasTarget, IEnumerable<string> onSource, IEnumerable<string> onTarget)
        {
            var ons = onSource.Zip(onTarget, (s, t) => string.Format("[{0}].{1} = [{2}].{3}", aliasSource, s, aliasTarget, t));
            var onClause = string.Join(" And ", ons);

            return string.Format("Left Join [{0}] [{1}] On {2}{3}", tableSource, aliasSource, onClause, Environment.NewLine);
        }

        public string GetSelect(string fromTable, string fromAlias, string whereClause, IEnumerable<IMapColumn> columns, IEnumerable<string> joins)
        {
            var columnClause = string.Join(", ", columns.Select(SqlColumn));
            var joinClause = joins == null ? string.Empty : string.Join(string.Empty, joins);
            whereClause = string.IsNullOrWhiteSpace(whereClause)
                ? string.Empty 
                : string.Format("Where {0}{1}", whereClause, Environment.NewLine);

            return string.Format("Select {0}{5}From [{1}] [{2}]{5}{3}{4}", columnClause, fromTable, fromAlias, joinClause, whereClause, Environment.NewLine);
        }

        #region Private Methods

        private static string ToHex(IEnumerable<byte> data)
        {
            var sb = new StringBuilder("0x");

            foreach (var t in data)
            {
                sb.Append(t.ToString("X2"));
            }
            return sb.ToString();
        }

        private void LoadValue(IMapColumn column)
        {
            column.Value = column.Reference == null ? Translate(column.Value) : string.Format("{0}{1}", ParameterPrefix, column.Reference);
        }

        private IMapColumn[] LoadValues(IEnumerable<IMapColumn> columns)
        {
            var array = columns.ToArray();
            foreach (var column in array)
            {
                LoadValue(column);
            }
            return array;
        }

        private static string SqlValues(IEnumerable<string> columns, IEnumerable<object> values)
        {
            var cols = string.Join("],[", columns);
            var vals = string.Join(",", values);

            return string.Format("([{0}]) Values ({1})", cols, vals);
        }

        private string SqlWhere(IEnumerable<string> columns, IEnumerable<object> values)
        {
            return string.Join(" and ", columns.Zip(values, (c, v) => string.Format("[{0}]={1}", c, EscapeSql(v))));
        }

        private string SqlSet(IEnumerable<string> columns, IEnumerable<object> values)
        {
            return string.Join(",", columns.Zip(values, (n, v) => string.Format("[{0}]={1}", n, EscapeSql(v))));
        }

        private string SqlParam(IEnumerable<string> columns, IEnumerable<object> values)
        {
            return string.Join(",", columns.Zip(values, (n, v) => string.Format("@{0}={1}", n, EscapeSql(v))));
        }

        private string SqlColumn(IMapColumn column)
        {
            return string.Format("[{0}].[{1}] As {2}", column.TableAlias, column.Name, column.Alias);
        }

        #endregion
    }
}